package com.scalar.chiptrack.adaptor.ikanos.asem;

import com.scalar.chiptrack.jobs.JobHandler;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.adaptor.AdaptorUtils;
import com.scalar.chiptrack.adaptor.AssemblyLot;
import com.scalar.chiptrack.adaptor.InventoryLot;
import com.scalar.chiptrack.adaptor.ShippedLot;
import com.scalar.chiptrack.adaptor.dao.AdaptorDAO;
import com.scalar.chiptrack.adaptor.dao.AssemblyAdaptorDAO;
import com.scalar.chiptrack.adaptor.dao.InventoryLotDAO;
import com.scalar.chiptrack.adaptor.dao.ShippedLotDAO;
import com.scalar.chiptrack.adaptor.dao.NewAdaptorDAO;
import com.scalar.chiptrack.adaptor.dao.AdaptorRunInfo;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.DateUtil;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.ScalarException;

import java.util.HashMap;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.Date;
import java.io.File;

import jxl.Sheet;
import jxl.Workbook;
import jxl.Cell;
import jxl.CellType;

/**
 * User: ChandraSekhar
 * Date: Jul 21, 2008
 * Time: 6:26:38 PM
 */
public class ASEMDataParser extends JobHandler implements AdaptorConstants
{
    HashMap m_assemblyDataMap           = new HashMap();
    HashMap m_dieBankDataMap            = new HashMap();
    HashMap m_assyShipDataMap           = new HashMap();
    private Logger m_parserLogger       = null;
    private String m_dataOutputDir      = null;

    private DBTransaction dbTransaction = null;

    Calendar cal = Calendar.getInstance();
    int thisYear = cal.get( Calendar.YEAR );
    int thisMonth = cal.get( Calendar.MONTH ) + 1 ;

    public ASEMDataParser() throws ScalarException
    {
        try
        {
            m_dataOutputDir = AdaptorUtils.getDataOutputDir(VENDOR_NAME_ASEM, WIP_TYPE_ASSEMBLY);
            AdaptorUtils.makeAllAdaptorDirs(m_dataOutputDir);
            m_parserLogger = AdaptorUtils.getDataParserLogger(m_dataOutputDir,VENDOR_NAME_ASEM);
            AssemblyAdaptorDAO.setLogger(m_parserLogger);
            InventoryLotDAO.setLogger(m_parserLogger);
            ShippedLotDAO.setLogger( m_parserLogger);
            AdaptorUtils.setLogger(m_parserLogger);
        }
        catch (ScalarException se)
        {
            log("ERROR!! ERROR!! ERROR!! Exception while loading ASEM WIP output directory details from database, Exception: " + se);
            throw se;
        }
    }

    public static void main(String args[]) throws Exception
    {
       new ASEMDataParser().execute();
    }


    public void execute() throws Exception
    {
        AdaptorUtils.printHeader(VENDOR_NAME_ASEM, CONSTANT_PARSER);

        File doneDestDir = new File(m_dataOutputDir + "/done");

        File wipDataoutDir = new File(m_dataOutputDir);

        File[] fileList = wipDataoutDir.listFiles();

        if ((fileList == null) || (fileList.length <= 3))
        {
            log("No file found in directory: " + wipDataoutDir);
            throw new ScalarException("No file found in directory: " + wipDataoutDir + "----\n\n");
        }

        ArrayList successFileList = new ArrayList();
        boolean success = false;

        //PARSING STARTS FROM HERE
        for (int i = 0; i < fileList.length; i++)
        {
            if (fileList[i].isDirectory() == true)
            {
                continue;
            }
            try
            {
                if (fileList[i].getName() != null && ( fileList[i].getName().trim().startsWith(ASEMPOP3Adaptor.FILE_NAME_WIP ) ) )
                {

                    ArrayList allWipLotList = parseAssyWipSheet( fileList[i].getAbsolutePath() );

                    if (allWipLotList != null && allWipLotList.size() > 0)
                    {
                        getWipStatusInfoList(allWipLotList);
                    }

                    //Add files to success list to move to /done directory
                    successFileList.add(fileList[i]);
                }
                if ( fileList[i].getName() != null && fileList[i].getName().trim().startsWith( ASEMPOP3Adaptor.FILE_NAME_SHIP ) )
                {
                    // To parse the the ASEM SHIP_STATUS sheet
                    ArrayList shipList = parseASEMShipReport( fileList[i].getAbsolutePath() );


                    if ( shipList != null && shipList.size() > 0 )
                    {
                        //to get the ASEK SHip Details details
                        getASEMShipDetails( shipList );
                    }

                    successFileList.add( fileList[i] );
                }
                success = true;
            }
            catch( Exception e )
            {
                success = false;
                log("Exception while parsing file: " + fileList[i].getAbsolutePath() + "\nException: " + e);
                e.printStackTrace();
                throw e;
            }
        }
        /*Updating the Die Bank Lot Information*/
        try
        {
            // Save Die Bank Information
            if (m_dieBankDataMap.isEmpty() == false)
            {
                log(" Updating Die Bank Lot Information");
                InventoryLotDAO.saveInventoryLot(null, m_dieBankDataMap.values().iterator(), LOCATION_TYPE_DIE_BANK, VENDOR_NAME_ASEM);
            }
            else
            {
                InventoryLotDAO.closeInventoryLot(null, LOCATION_TYPE_DIE_BANK, VENDOR_NAME_ASEM);
            }
            success = true;
            m_dieBankDataMap.clear();
            m_dieBankDataMap = null;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log("ERROR!! ERROR!! ERROR!! Could not update Die Bank Lot information into database: " + se);
            throw se;
        }

        /*Updating the Assembly Lot Information*/
        try
        {
            // Step 2
            // Save Assembly Information
            if (m_assemblyDataMap.isEmpty() == false)
            {
                log(" Updating Assembly Lot Information");
                AssemblyAdaptorDAO.saveAssemblyLot(null, m_assemblyDataMap.values().iterator(),WIP_TYPE_ASSEMBLY, VENDOR_NAME_ASEM);
            }
            else
            {
                AssemblyAdaptorDAO.closeAssemblyLot(dbTransaction, WIP_TYPE_ASSEMBLY, VENDOR_NAME_ASEM);
            }

            m_assemblyDataMap.clear();
            m_assemblyDataMap = null;
            success = true;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log("ERROR!! ERROR!! ERROR!! Could not update Assembly Lot information into database: " + se);
            throw se;
        }

        // Save Assembly Ship Information
        try
        {
            if ( m_assyShipDataMap.isEmpty() == false )
            {
                log( " Updating Assembly Shiping Lot Information" );
                ShippedLotDAO.saveShippedLot( null, m_assyShipDataMap.values().iterator() );
            }
            success = true;
            m_assyShipDataMap.clear();
            m_assyShipDataMap = null;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log( "ERROR!! ERROR!! ERROR!! Could not update Assembly Shipping Lot information into database: " + se );
            throw se;
        }

        // To Close All Drop Out Lots
        AdaptorDAO.closeDropOutLots(VENDOR_NAME_ASEM);

        // TO SEND THE NEW LOTS, NEW TRANSIT LOTS, NO PO LOTS, DISCREPANCY LOTS NOTIFICATIONS TO THE USER
        AdaptorDAO.sendAllNotifications(VENDOR_NAME_ASEM, true, true, true);


        int adaptor_id = AdaptorRunInfo.getLastRunAdaptorId( ASEMPOP3Adaptor.ADAPTOR_NAME, CONSTANT_ADAPTOR);

        // Update Adaptor_Run_Info table
        try
        {
            if ( success == true )
            {
                AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, ASEMPOP3Adaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
                System.out.println( "SUCCESSFULLY UPDATED IN THE DATABASE" );
            }
        }
        catch ( ScalarException se )
        {
            success = false; //As filES DATA SAVED , UPDATEING AS SUCCESS
            AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, ASEMPOP3Adaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
            log( "WARNING!! WARNING!! WARNING!! Could not update Adaptor run information, Exception: " + se );
            throw se;
        }
       // Step 7
       // Move the successful files to 'done' directory
       if ( success )
       {
           AdaptorUtils.moveFilesToDone(successFileList, doneDestDir );
       }
    }//end of execute method

    /**
    *
    * @param allWipLotList
    */
    public void getWipStatusInfoList( ArrayList allWipLotList )  throws Exception
    {
        String partNumber           = null;
        String lotNumber            = null;
        String fablotNumber         = null;
        ASEMWipStatus wipStatus     = null;
        AssemblyLot assemblyLot     = null;
        InventoryLot inventoryLot   = null;

        try
        {
            Iterator asemIterator = allWipLotList.iterator();

            while (asemIterator.hasNext())
            {
                wipStatus = (ASEMWipStatus) asemIterator.next();

                partNumber    = wipStatus.getDeviceName();
                lotNumber     = wipStatus.getLotNumber();
                fablotNumber  = wipStatus.getFabLotNumber();

                if (lotNumber == null)
                {
                   lotNumber = fablotNumber;
                }

                String assyExpdateOut = null;
                if ( wipStatus.getAsod() != null && wipStatus.getAsod().trim().length() < 8 )
                {
                    String[] inputDate = wipStatus.getAsod().split("/");
                    if( inputDate != null && inputDate.length > 0 )
					{
						int Month = Integer.parseInt( inputDate[0] );
						if ( Month > thisMonth )
						{
							assyExpdateOut = wipStatus.getAsod() + "/" + ( thisYear-1);
						}
						else
						{
							assyExpdateOut = wipStatus.getAsod() + "/" + thisYear;
						}
					}
                }
                if ( assyExpdateOut == null && wipStatus.getRsod() != null && wipStatus.getRsod().trim().length() < 8 )
                {
                    String[] inputDate = wipStatus.getRsod().split("/");
                    if( inputDate != null && inputDate.length > 0 )
					{
						int Month = Integer.parseInt( inputDate[0] );
						if ( Month > thisMonth )
						{
							assyExpdateOut = wipStatus.getRsod() + "/" + ( thisYear-1);
						}
						else
						{
							assyExpdateOut = wipStatus.getRsod() + "/" + thisYear;
						}
					}
                }

                if ( partNumber != null && lotNumber != null && fablotNumber != null )
                {
                    if ( wipStatus.getDieBankQuantity() != null || wipStatus.getIqa() != null)
                    {
                        /*int bomPocessstep_id = NewAdaptorDAO.getBOMProcessStepId( partNumber , WIP_TYPE_ASSEMBLY );

                        if ( bomPocessstep_id > 0 )
                        {
                            log ( "In BOM Die bank--> Assembly Bom " + lotNumber );
                            assemblyLot = (AssemblyLot) m_assemblyDataMap.get( partNumber + lotNumber + fablotNumber );
                            if (assemblyLot == null)
                            {
                                assemblyLot = new AssemblyLot( VENDOR_NAME_ASEM,  WIP_TYPE_ASSEMBLY );
                                m_assemblyDataMap.put( partNumber + lotNumber + fablotNumber, assemblyLot);
                            }
                            if (wipStatus.getDieBankQuantity() != null )
                            {
                                assemblyLot = (AssemblyLot) m_assemblyDataMap.get( partNumber + lotNumber + fablotNumber );

                                if (assemblyLot.getQtyIn() != null)
                                {
                                    try
                                    {
                                        assemblyLot.setQtyIn((Integer.parseInt(assemblyLot.getQtyIn()) + Integer.parseInt(wipStatus.getDieBankQuantity())) + "");

                                    }
                                    catch (Exception e)
                                    {
                                        assemblyLot.setQtyIn(wipStatus.getDieBankQuantity());
                                    }
                                }
                                else
                                {
                                    assemblyLot.setQtyIn(wipStatus.getDieBankQuantity());
                                }

                                if (assemblyLot.getStage() != null && !"DBANK".equalsIgnoreCase(assemblyLot.getStage()))
                                {
                                    assemblyLot.setStage(assemblyLot.getStage() + ", DBANK");
                                }
                                else
                                {
                                    assemblyLot.setStage("DBANK");
                                }
                            }
                            if (wipStatus.getIqa() != null )
                            {
                                assemblyLot = (AssemblyLot) m_assemblyDataMap.get( partNumber + lotNumber + fablotNumber );

                                if (assemblyLot.getQtyIn() != null)
                                {
                                    try
                                    {
                                        assemblyLot.setQtyIn((Integer.parseInt(assemblyLot.getQtyIn()) + Integer.parseInt(wipStatus.getIqa())) + "");

                                    }
                                    catch (Exception e)
                                    {
                                        assemblyLot.setQtyIn(wipStatus.getIqa());
                                    }
                                }
                                else
                                {
                                    assemblyLot.setQtyIn(wipStatus.getIqa());
                                }

                                if (assemblyLot.getStage() != null && !"IQA".equalsIgnoreCase(assemblyLot.getStage()))
                                {
                                    assemblyLot.setStage(assemblyLot.getStage() + ", IOA");
                                }
                                else
                                {
                                    assemblyLot.setStage("IOA");
                                }
                            }

                            if ( assemblyLot.getQtyIn() != null && !"0".equalsIgnoreCase( assemblyLot.getQtyIn() ))
                            {
                                assemblyLot.setDeviceName(partNumber);
                                assemblyLot.setPackageType(wipStatus.getPackageType());
                                assemblyLot.setLeadCount(wipStatus.getLeadCount() );
                                assemblyLot.setLotNumber(lotNumber);
                                assemblyLot.setStatus(LOT_STATUS_RUNNING);
                                assemblyLot.setFabLotNumber(fablotNumber);
                                if (wipStatus.getLotType() != null && "E".equalsIgnoreCase(wipStatus.getLotType() ) )
                                {
                                    assemblyLot.setCategory( LOT_CATEGORY_TYPE_ENGG );
                                }
                                else
                                {
                                      assemblyLot.setCategory( LOT_CATEGORY_TYPE_PROD );
                                }
                                if ( wipStatus.getIssueDate() != null )
                                {
                                    assemblyLot.setDateIn( StringUtils.formatDate(wipStatus.getIssueDate(), "MM/dd/yy","MM/dd/yyyy" ) );
                                }
                                else if ( wipStatus.getReceiveDate() != null)
                                {
                                    assemblyLot.setDateIn( StringUtils.formatDate(wipStatus.getReceiveDate(), "MM/dd/yy","MM/dd/yyyy" ) );
                                }
                                else
                                {
                                     assemblyLot.setDateIn( DateUtil.formatDate(new Date(), "MM/dd/yyyy") );
                                }

                                assemblyLot.setExpectedDateOut( assyExpdateOut );
                            }
                        }
                        else
                        {*/
                            log( fablotNumber +" In DieBank");
                            
                            if ( partNumber.endsWith("-00") )
                            {
                                partNumber = partNumber.substring(0, partNumber.lastIndexOf("-00") );
                            }
                            inventoryLot = (InventoryLot)m_dieBankDataMap.get( partNumber + fablotNumber );

                            if ( inventoryLot == null )
                            {
                                inventoryLot = new InventoryLot( VENDOR_NAME_ASEM, LOCATION_TYPE_DIE_BANK );
                                m_dieBankDataMap.put( partNumber + fablotNumber, inventoryLot );
                            }

                            if (wipStatus.getDieBankQuantity() != null )
                            {
                                inventoryLot = (InventoryLot)m_dieBankDataMap.get( partNumber + fablotNumber );

                                if (inventoryLot.getQtyIn() != null)
                                {
                                    try
                                    {
                                        inventoryLot.setQtyIn( (Integer.parseInt(inventoryLot.getQtyIn()) + Integer.parseInt(wipStatus.getDieBankQuantity())) + "");
                                        if ( inventoryLot.getWaferQtyIn() != null && wipStatus.getWaferQuantity() != null && wipStatus.getWaferQuantity().trim().length() > 0 )
                                        {
                                            inventoryLot.setWaferQtyIn((Integer.parseInt(inventoryLot.getWaferQtyIn()) + Integer.parseInt(wipStatus.getWaferQuantity())) + "");
                                        }
                                    }
                                    catch (Exception e)
                                    {
                                        inventoryLot.setQtyIn(wipStatus.getDieBankQuantity());
                                        inventoryLot.setWaferQtyIn( wipStatus.getWaferQuantity() );
                                    }
                                }
                                else
                                {
                                    inventoryLot.setQtyIn(wipStatus.getDieBankQuantity());
                                    inventoryLot.setWaferQtyIn( wipStatus.getWaferQuantity() );
                                }

                                if (inventoryLot.getStage() != null && !"DBANK".equalsIgnoreCase(inventoryLot.getStage()))
                                {
                                    inventoryLot.setStage(inventoryLot.getStage() + ", DBANK");
                                }
                                else
                                {
                                    inventoryLot.setStage("DBANK");
                                }
                            }
                            if (wipStatus.getIqa() != null )
                            {
                                inventoryLot = (InventoryLot)m_dieBankDataMap.get( partNumber + fablotNumber );

                                if (inventoryLot.getQtyIn() != null)
                                {
                                    try
                                    {
                                        inventoryLot.setQtyIn((Integer.parseInt(inventoryLot.getQtyIn()) + Integer.parseInt(wipStatus.getIqa())) + "");
                                        if ( inventoryLot.getWaferQtyIn() != null )
                                        {
                                            inventoryLot.setWaferQtyIn((Integer.parseInt(inventoryLot.getWaferQtyIn()) + Integer.parseInt(wipStatus.getWaferQuantity())) + "");
                                        }   
                                    }
                                    catch (Exception e)
                                    {
                                        inventoryLot.setQtyIn(wipStatus.getIqa());
                                        inventoryLot.setWaferQtyIn( wipStatus.getWaferQuantity() );
                                    }
                                }
                                else
                                {
                                    inventoryLot.setQtyIn(wipStatus.getIqa());
                                    inventoryLot.setWaferQtyIn( wipStatus.getWaferQuantity() );
                                }

                                if (inventoryLot.getStage() != null && !"IQA".equalsIgnoreCase(inventoryLot.getStage()))
                                {
                                    inventoryLot.setStage(inventoryLot.getStage() + ", IOA");
                                }
                                else
                                {
                                    inventoryLot.setStage("IOA");
                                }
                            }

                            if (inventoryLot.getQtyIn() != null && !"0".equalsIgnoreCase( inventoryLot.getQtyIn() ) )
                            {
                                inventoryLot.setPartNumber( partNumber );
                                inventoryLot.setLotNumber( fablotNumber );

                                if ( wipStatus.getReceiveDate() != null )
                                {
                                    inventoryLot.setDateIn( StringUtils.formatDate(wipStatus.getReceiveDate(), "MM/dd/yy","MM/dd/yyyy" ) );
                                }

                                inventoryLot.setFabLotNumber( fablotNumber );
                                inventoryLot.setPkgType( wipStatus.getPackageType() );
                                inventoryLot.setLeadCount( wipStatus.getLeadCount() );
                                inventoryLot.setStatus( LOT_STATUS_RUNNING );
                                if (wipStatus.getLotType() != null && "E".equalsIgnoreCase(wipStatus.getLotType() ) )
                                {
                                    inventoryLot.setLotCategory( LOT_CATEGORY_TYPE_ENGG );
                                }
                                else
                                {
                                   inventoryLot.setLotCategory( LOT_CATEGORY_TYPE_PROD );
                                }
                            }
                        //}
                    }
                    /*Fetching Assembly Lots*/

                    if (  wipStatus.getFol() != null || wipStatus.getMolding() != null || wipStatus.getDjktm() != null
                            || wipStatus.getSilscn() != null || wipStatus.getFvi() != null || wipStatus.getFgst() != null || wipStatus.getTrstQty() != null )
                    {
                        log("In Assembly" + lotNumber );
                        
                        assemblyLot = (AssemblyLot)m_assemblyDataMap.get( partNumber + lotNumber + fablotNumber );

                        if (assemblyLot == null)
                        {
                            assemblyLot = new AssemblyLot(VENDOR_NAME_ASEM, WIP_TYPE_ASSEMBLY);
                            m_assemblyDataMap.put(partNumber + lotNumber + fablotNumber, assemblyLot);
                        }

                        if (wipStatus.getFol() != null )
                        {
                            assemblyLot = (AssemblyLot) m_assemblyDataMap.get(partNumber + lotNumber + fablotNumber);

                            if (assemblyLot.getQtyIn() != null)
                            {
                                try
                                {
                                    assemblyLot.setQtyIn((Integer.parseInt(assemblyLot.getQtyIn()) + Integer.parseInt(wipStatus.getFol())) + "");
                                }
                                catch (Exception e)
                                {
                                    assemblyLot.setQtyIn(wipStatus.getFol());
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn(wipStatus.getFol());
                            }

                            if (assemblyLot.getStage() != null)
                            {
                                assemblyLot.setStage(assemblyLot.getStage() + ", FOL");
                            }
                            else
                            {
                                assemblyLot.setStage("FOL");
                            }
                        }
                        
                        if (wipStatus.getMolding() != null )
                        {
                            assemblyLot = (AssemblyLot) m_assemblyDataMap.get(partNumber + lotNumber + fablotNumber);

                            if (assemblyLot.getQtyIn() != null)
                            {
                                try
                                {
                                    assemblyLot.setQtyIn((Integer.parseInt(assemblyLot.getQtyIn()) + Integer.parseInt(wipStatus.getMolding())) + "");
                                }
                                catch (Exception e)
                                {
                                    assemblyLot.setQtyIn(wipStatus.getMolding());
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn(wipStatus.getMolding());
                            }

                            if (assemblyLot.getStage() != null)
                            {
                                assemblyLot.setStage(assemblyLot.getStage() + ", Molding");
                            }
                            else
                            {
                                assemblyLot.setStage("Molding");
                            }
                        }
                        if (wipStatus.getDjktm() != null )
                        {
                            assemblyLot = (AssemblyLot) m_assemblyDataMap.get(partNumber + lotNumber + fablotNumber);

                            if (assemblyLot.getQtyIn() != null)
                            {
                                try
                                {
                                    assemblyLot.setQtyIn((Integer.parseInt(assemblyLot.getQtyIn()) + Integer.parseInt(wipStatus.getDjktm())) + "");
                                }
                                catch (Exception e)
                                {
                                    assemblyLot.setQtyIn(wipStatus.getDjktm());
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn(wipStatus.getDjktm());
                            }

                             if (assemblyLot.getStage() != null)
                            {
                                assemblyLot.setStage(assemblyLot.getStage() + ", Dejunk Trim");
                            }
                            else
                            {
                                assemblyLot.setStage("Dejunk Trim");
                            }

                        }
                        if (wipStatus.getSilscn() != null )
                        {
                            assemblyLot = (AssemblyLot) m_assemblyDataMap.get(partNumber + lotNumber + fablotNumber);

                            if (assemblyLot.getQtyIn() != null)
                            {
                                try
                                {
                                    assemblyLot.setQtyIn((Integer.parseInt(assemblyLot.getQtyIn()) + Integer.parseInt(wipStatus.getSilscn())) + "");
                                }
                                catch (Exception e)
                                {
                                    assemblyLot.setQtyIn(wipStatus.getSilscn());
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn(wipStatus.getSilscn());
                            }

                            if (assemblyLot.getStage() != null)
                            {
                                assemblyLot.setStage(assemblyLot.getStage() + ", Laser Scan");
                            }
                            else
                            {
                                assemblyLot.setStage("Laser Scan");
                            }
                        }
                        if (wipStatus.getFvi() != null )
                        {
                            assemblyLot = (AssemblyLot) m_assemblyDataMap.get(partNumber + lotNumber + fablotNumber);

                            if (assemblyLot.getQtyIn() != null)
                            {
                                try
                                {
                                    assemblyLot.setQtyIn((Integer.parseInt(assemblyLot.getQtyIn()) + Integer.parseInt(wipStatus.getFvi())) + "");
                                }
                                catch (Exception e)
                                {
                                    assemblyLot.setQtyIn(wipStatus.getFvi());
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn(wipStatus.getFvi());
                            }

                            if (assemblyLot.getStage() != null)
                            {
                                assemblyLot.setStage(assemblyLot.getStage() + ", Final Visual Inspection");
                            }
                            else
                            {
                                assemblyLot.setStage("Final Visual Inspection");
                            }
                        }
                        if (wipStatus.getFgst() != null )
                        {
                            assemblyLot = (AssemblyLot) m_assemblyDataMap.get(partNumber + lotNumber + fablotNumber);

                            if (assemblyLot.getQtyIn() != null)
                            {
                                try
                                {
                                    assemblyLot.setQtyIn((Integer.parseInt(assemblyLot.getQtyIn()) + Integer.parseInt(wipStatus.getFgst())) + "");
                                }
                                catch (Exception e)
                                {
                                    assemblyLot.setQtyIn(wipStatus.getFgst());
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn(wipStatus.getFgst());
                            }
                            if (assemblyLot.getStage() != null)
                            {
                                assemblyLot.setStage(assemblyLot.getStage() + ", Assy FG");
                            }
                            else
                            {
                                assemblyLot.setStage("Assy FG");
                            }

                        }
                        if (wipStatus.getTrstQty() != null )
                        {
                            assemblyLot = (AssemblyLot) m_assemblyDataMap.get(partNumber + lotNumber + fablotNumber);

                            if (assemblyLot.getQtyIn() != null)
                            {
                                try
                                {
                                    assemblyLot.setQtyIn((Integer.parseInt(assemblyLot.getQtyIn()) + Integer.parseInt(wipStatus.getTrstQty())) + "");
                                }
                                catch (Exception e)
                                {
                                    assemblyLot.setQtyIn(wipStatus.getTrstQty());
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn(wipStatus.getTrstQty());
                            }
                            if (assemblyLot.getStage() != null)
                            {
                                assemblyLot.setStage(assemblyLot.getStage() + ", Ready To Ship");
                            }
                            else
                            {
                                assemblyLot.setStage("Ready To Ship");
                            }
                        }
                        
                        if ( assemblyLot.getQtyIn() != null && !"0".equalsIgnoreCase( assemblyLot.getQtyIn() ))
                        {
                            assemblyLot.setDeviceName(partNumber);
                            assemblyLot.setPackageType(wipStatus.getPackageType());
                            assemblyLot.setLeadCount(wipStatus.getLeadCount() );
                            assemblyLot.setLotNumber(lotNumber);
                            assemblyLot.setStatus(LOT_STATUS_RUNNING);
                            assemblyLot.setFabLotNumber(fablotNumber);
                            if (wipStatus.getLotType() != null && "E".equalsIgnoreCase(wipStatus.getLotType() ) )
                            {
                                assemblyLot.setCategory( LOT_CATEGORY_TYPE_ENGG );
                            }
                            else
                            {
                                  assemblyLot.setCategory( LOT_CATEGORY_TYPE_PROD );
                            }
                            if ( wipStatus.getIssueDate() != null )
                            {
                                assemblyLot.setDateIn( StringUtils.formatDate(wipStatus.getIssueDate(), "MM/dd/yy","MM/dd/yyyy" ) );
                            }
                            else if ( wipStatus.getReceiveDate() != null)
                            {
                                assemblyLot.setDateIn( StringUtils.formatDate(wipStatus.getReceiveDate(), "MM/dd/yy","MM/dd/yyyy" ) );
                            }

                            assemblyLot.setExpectedDateOut( assyExpdateOut );
                        }
                    }

                }
            }
        }
        catch (Exception e)
        {
           e.printStackTrace();

           log("ERROR!ERROR! While parsing the ASEM Assy Wip Details" + e);
           System.out.println("ERROR!ERROR! While parsing the ASEM Assy Wip Details" + e);
           throw e;
        }
    }


    /**
     *
     * @param fileName
     * @return
     * @throws Exception
     */
    private ArrayList parseAssyWipSheet(String fileName ) throws Exception
    {
        ASEMWipStatus wipStatus = null;
        ArrayList wipList       = new ArrayList();
        Sheet sheet             = null;
        String checkValue       = null;

        if (fileName == null)
        {
            throw new ScalarException("Filename argument is null");
        }

        try
        {
            File wipStatusFile = new File(fileName);
            if (wipStatusFile.exists() == false)
            {
                throw new ScalarException(fileName + " doesnt exist");
            }

            log("\nFound " + fileName);
            log("Parsing the file for ASEM WIP Report...");
            System.out.println("\nFound " + fileName);
            System.out.println("Parsing the file for ASEM WIP Report...");

            Workbook workbook = Workbook.getWorkbook(wipStatusFile);

            sheet = workbook.getSheet(0);

            Cell[] row = null;

            for (int rowCount = 9; rowCount < sheet.getRows(); rowCount++)
            {
                row = sheet.getRow(rowCount);

                if (row != null && row.length > 15)
                {
                    checkValue = row[0].getContents().trim();

                    if (checkValue != null && checkValue.indexOf("TOTAL") == -1 )
                    {
                        wipStatus = new ASEMWipStatus();

                        if (row.length > 0)
                        {
                            if (row[0].getType().equals(CellType.EMPTY))
                            {
                                wipStatus.setDeviceName(null);
                            }
                            else
                            {
                                wipStatus.setDeviceName(row[0].getContents().trim());
                            }
                        }
                        if (row.length > 1)
                        {
                            if (row[1].getType().equals(CellType.EMPTY))
                            {
                                wipStatus.setPackageType(null);
                            }
                            else
                            {
                                wipStatus.setPackageType(row[1].getContents().trim());
                            }
                        }
                        if (row.length > 2)
                        {
                            if (row[2].getType().equals(CellType.EMPTY))
                            {
                                wipStatus.setLeadCount(null);
                            }
                            else
                            {
                                wipStatus.setLeadCount(row[2].getContents().trim());
                            }
                        }
                        if (row.length > 3)
                        {
                            if (row[3].getType().equals(CellType.EMPTY))
                            {
                                wipStatus.setFabLotNumber(null);
                            }
                            else
                            {
                                wipStatus.setFabLotNumber(row[3].getContents().trim());
                            }
                        }
                        if (row.length > 4)
                        {
                            if (row[4].getType().equals(CellType.EMPTY))
                            {
                                wipStatus.setLotNumber(null);
                            }
                            else
                            {
                                wipStatus.setLotNumber(row[4].getContents().trim());
                            }
                        }
                        if (row.length > 5)
                        {
                            if (row[5].getType().equals(CellType.EMPTY))
                            {
                                wipStatus.setReceiveDate(null);
                            }
                            else
                            {
                                wipStatus.setReceiveDate(row[5].getContents().trim());
                            }
                        }
                        if (row.length > 6)
                        {
                            if (row[6].getType().equals(CellType.EMPTY))
                            {
                                wipStatus.setIssueDate(null);
                            }
                            else
                            {
                                wipStatus.setIssueDate(row[6].getContents().trim());
                            }
                        }
                        if (row.length > 7)
                        {
                            if (row[7].getType().equals(CellType.EMPTY))
                            {
                                wipStatus.setIqa(null);
                            }
                            else
                            {
                                wipStatus.setIqa(row[7].getContents().trim());
                            }
                        }
                        if (row.length > 8)
                        {
                            if (row[8].getType().equals(CellType.EMPTY))
                            {
                                wipStatus.setWaferQuantity(null);
                            }
                            else
                            {
                                wipStatus.setWaferQuantity(row[8].getContents().trim());
                            }
                        }
                        if (row.length > 9)
                        {
                            if (row[9].getType().equals(CellType.EMPTY))
                            {
                                wipStatus.setDieBankQuantity(null);
                            }
                            else
                            {
                                wipStatus.setDieBankQuantity(row[9].getContents().trim());
                            }
                        }
                        if (row.length > 10)
                        {
                            if (row[10].getType().equals(CellType.EMPTY))
                            {
                                wipStatus.setScheduleNumber(null);
                            }
                            else
                            {
                                wipStatus.setScheduleNumber(row[10].getContents().trim());
                            }
                        }
                        if (row.length > 11)
                        {
                            if (row[11].getType().equals(CellType.EMPTY))
                            {
                                wipStatus.setFol(null);
                            }
                            else
                            {
                                wipStatus.setFol(row[11].getContents().trim());
                            }
                        }
                        if (row.length > 12)
                        {
                            if (row[12].getType().equals(CellType.EMPTY))
                            {
                                wipStatus.setMolding(null);
                            }
                            else
                            {
                                wipStatus.setMolding(row[12].getContents().trim());
                            }
                        }
                        if (row.length > 13)
                        {
                            if (row[13].getType().equals(CellType.EMPTY))
                            {
                                wipStatus.setDjktm(null);
                            }
                            else
                            {
                                wipStatus.setDjktm(row[13].getContents().trim());
                            }
                        }
                        if (row.length > 14)
                        {
                            if (row[14].getType().equals(CellType.EMPTY))
                            {
                                wipStatus.setSilscn(null);
                            }
                            else
                            {
                                wipStatus.setSilscn(row[14].getContents().trim());
                            }
                        }
                        if (row.length > 15)
                        {
                            if (row[15].getType().equals(CellType.EMPTY))
                            {
                                wipStatus.setFvi(null);
                            }
                            else
                            {
                                wipStatus.setFvi(row[15].getContents().trim());
                            }
                        }
                        //ROW 16 TO  ROW 23 not Required as Belongs TEST process
                        if (row.length > 24)
                        {
                            if (row[24].getType().equals(CellType.EMPTY))
                            {
                                wipStatus.setFgst(null);
                            }
                            else
                            {
                                wipStatus.setFgst(row[24].getContents().trim());
                            }
                        }
                        //ROW 25 TOTAL NOT REQUIRED
                        if (row.length > 26)
                        {
                            if (row[26].getType().equals(CellType.EMPTY))
                            {
                                wipStatus.setAsod(null);
                            }
                            else
                            {
                                wipStatus.setAsod(row[26].getContents().trim());
                            }
                        }
                        if (row.length > 27)
                        {
                            if (row[27].getType().equals(CellType.EMPTY))
                            {
                                wipStatus.setRsod(null);
                            }
                            else
                            {
                                wipStatus.setRsod(row[27].getContents().trim());
                            }
                        }

                        if (row.length > 28)
                        {
                            if (row[28].getType().equals(CellType.EMPTY))
                            {
                                wipStatus.setTrst(null);
                            }
                            else
                            {
                                wipStatus.setTrst(row[28].getContents().trim());
                            }
                        }
                        if (row.length > 29)
                        {
                            if (row[29].getType().equals(CellType.EMPTY))
                            {
                                wipStatus.setTrstQty(null);
                            }
                            else
                            {
                                wipStatus.setTrstQty(row[29].getContents().trim());
                            }
                        }
                        if (row.length > 30)
                        {
                            if (row[30].getType().equals(CellType.EMPTY))
                            {
                                wipStatus.setLotType(null);
                            }
                            else
                            {
                                wipStatus.setLotType(row[30].getContents().trim());
                            }
                        }
                        //ROW 31 ROW 32 Not Required
                        if ( wipStatus.getDeviceName() != null && wipStatus.getDeviceName().length() > 0
                                && wipStatus.getFabLotNumber() != null && wipStatus.getFabLotNumber().length() > 0 )
                        {
                            wipList.add(wipStatus);
                        }
                    }
                }
            }
        }
        catch (Exception e)
        {
            System.out.println("!!ERROR !ERROR....While Reading the ASEM Assy Wip Sheet........");
            e.printStackTrace();
            throw new ScalarException("Exception " + e);
        }

        return wipList;
    }
    private ArrayList parseASEMShipReport( String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );

        ArrayList shipList              = new ArrayList();
        ASEMShipStatus  shipStatus       = null;
        try
        {
            File detailWipFile = new File( fileName );
            if ( detailWipFile.exists() == false )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            log( "\nFound " + fileName );
            log( "\nParsing the file for ASEM Ship Info..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for ASEM Ship Info..." );

            Workbook workbook = Workbook.getWorkbook( detailWipFile );
            Sheet sheet = workbook.getSheet( 0 );
            Cell[] row = null;

            for ( int rowCount = 4; rowCount < sheet.getRows(); rowCount++ )
            {
                row = sheet.getRow( rowCount );

                shipStatus = new ASEMShipStatus();

                //row[0] To ROW[3] not Required

                if( row.length > 4)
                {
                    if ( row[4].getType().equals(CellType.EMPTY) )
                    {
                        shipStatus.setPoNumber( null );
                    }
                    else
                    {
                        shipStatus.setPoNumber( row[4].getContents().trim() );
                    }
                }
                if( row.length > 5)
                {
                    if ( row[5].getType().equals(CellType.EMPTY) )
                    {
                        shipStatus.setInvoiceNo( null );
                    }
                    else
                    {
                        shipStatus.setInvoiceNo( row[5].getContents().trim() );
                    }
                }
                //row[6] To ROW[9] not Required
                if( row.length > 10)
                {
                    if ( row[10].getType().equals(CellType.EMPTY) )
                    {
                        shipStatus.setFabLotNo( null );
                    }
                    else
                    {
                        shipStatus.setFabLotNo( row[10].getContents().trim());
                    }
                }
                if ( row.length > 11 )
                {
                   if ( row[11].getType().equals(CellType.EMPTY) )
                    {
                        shipStatus.setLotNumber( null );
                    }
                    else
                    {
                        shipStatus.setLotNumber( row[11].getContents().trim());
                    }
                }

                if( row.length > 12 )
                {
                    if ( row[12].getType().equals(CellType.EMPTY) )
                    {
                        shipStatus.setDeviceName( null );
                    }
                    else
                    {
                        shipStatus.setDeviceName( row[12].getContents().trim());
                    }
                }
                //ROW 13 MATERIAL not required
                //ROW 14 WAFER_PIECES not required
                if( row.length >15 )
                {
                    if ( row[15].getType().equals(CellType.EMPTY) )
                    {
                        shipStatus.setShipDieQty( null );
                    }
                    else
                    {
                        shipStatus.setShipDieQty( row[15].getContents().trim());
                    }
                }
                // ROW 16 BINCODE not required
                if ( row.length > 17 )
                {
                    if ( row[17].getType().equals(CellType.EMPTY) )
                    {
                        shipStatus.setShipDate( null );
                    }
                    else
                    {
                        shipStatus.setShipDate( row[17].getContents().trim() );
                    }
                }
                if( row.length > 18 )
                {
                    if ( row[18].getType().equals(CellType.EMPTY) )
                    {
                        shipStatus.setNextLocation( null );
                    }
                    else
                    {
                        shipStatus.setNextLocation( row[18].getContents().trim());
                    }
                }
                // ROW 19 DROP_FLAG not required
                if( row.length > 20 )
                {
                    if ( row[20].getType().equals(CellType.EMPTY) )
                    {
                        shipStatus.setFlightNo( null );
                    }
                    else
                    {
                        shipStatus.setFlightNo( row[20].getContents().trim());
                    }
                }
                if( row.length >21 )
                {
                    if ( row[21].getType().equals(CellType.EMPTY) )
                    {
                        shipStatus.setMawb( null );
                    }
                    else
                    {
                        shipStatus.setMawb( row[21].getContents().trim());
                    }
                }
                if( row.length >22 )
                {
                    if ( row[22].getType().equals(CellType.EMPTY) )
                    {
                        shipStatus.setHawb( null );
                    }
                    else
                    {
                        shipStatus.setHawb( row[22].getContents().trim());
                    }
                }
                if( row.length >23 )
                {
                    if ( row[23].getType().equals(CellType.EMPTY) )
                    {
                        shipStatus.setEtd( null );
                    }
                    else
                    {
                        shipStatus.setEtd( row[23].getContents().trim());
                    }
                }
                if( row.length >24 )
                {
                    if ( row[24].getType().equals(CellType.EMPTY) )
                    {
                        shipStatus.setEta( null );
                    }
                    else
                    {
                        shipStatus.setEta( row[24].getContents().trim());
                    }
                }
                //ROW [25] To ROW[27] not Required
                if( row.length >28 )
                {
                    if ( row[28].getType().equals(CellType.EMPTY) )
                    {
                        shipStatus.setLotType( null );
                    }
                    else
                    {
                        shipStatus.setLotType( row[28].getContents().trim());
                    }
                }

                if ( shipStatus.getDeviceName() != null && shipStatus.getDeviceName().length() >0
                     && shipStatus.getFabLotNo() != null && shipStatus.getFabLotNo().length() >0 )
                {
                    shipList.add( shipStatus );
                }
            }
        }
        catch ( Exception ex )
        {
            System.out.println("ERROR!!!ERROR!!! While Parsing the ASEM SHIP sheet");
            ex.printStackTrace();
            throw ex;
        }
        return shipList;
    }// end of prase Shipment Report method

    private void getASEMShipDetails(ArrayList shippingDataList) throws Exception
    {
       if ( shippingDataList == null || shippingDataList.size() == 0 )
       {
            return;
       }
       ASEMShipStatus shipStatus  = null;
       String partNumber                = null;
       String lotNumber                 = null;
       ShippedLot shippedLot          = null;
       String fabLotNumber              = null;
       String nextLocation              = null;
       try
       {
           Iterator iterator = shippingDataList.iterator();

           while ( iterator.hasNext() )
           {
               shipStatus = (ASEMShipStatus) iterator.next();

               lotNumber    = shipStatus.getLotNumber() ;
               partNumber   = shipStatus.getDeviceName();
               fabLotNumber = shipStatus.getFabLotNo();
               nextLocation = shipStatus.getNextLocation();
               String shipDate = null;

               if ( shipStatus.getShipDate() != null )
               {
                   shipDate = StringUtils.formatDate( shipStatus.getShipDate(), "yyyyMMdd", "MM/dd/yyyy");
               }

               if ( lotNumber != null && partNumber != null && fabLotNumber != null )
               {
                   shippedLot = ( ShippedLot ) m_assyShipDataMap.get ( partNumber + lotNumber + fabLotNumber + nextLocation + shipDate );

                   if ( shippedLot == null )
                   {
                        shippedLot = new ShippedLot(VENDOR_NAME_ASEM, LOCATION_TYPE_ASSEMBLY);
                        m_assyShipDataMap.put( partNumber + lotNumber + fabLotNumber + nextLocation + shipDate, shippedLot );
                   }

                   int quantity = 0;
                   if ( shippedLot.getShipQty() != null )
                   {
                       try
                       {
                           quantity = Integer.parseInt( shippedLot.getShipQty() );
                       }
                       catch ( NumberFormatException ignore )
                       {
                           quantity = 0;
                       }
                       try
                       {
                           quantity += Integer.parseInt( shipStatus.getShipDieQty() );
                       }
                       catch ( NumberFormatException ignore )
                       {
                           quantity = 0;
                       }
                   }
                   else
                   {
                       try
                       {
                           quantity = Integer.parseInt( shipStatus.getShipDieQty() );
                       }
                       catch ( NumberFormatException ignore )
                       {
                           quantity = 0;
                       }
                   }

                   if ( quantity > 0 )
                   {
                       shippedLot.setLotNumber( lotNumber );
                       shippedLot.setPartNumber( partNumber );
                       shippedLot.setShipDate( shipDate );
                       shippedLot.setShipQty( quantity + "" );
                       shippedLot.setNextLocation( nextLocation );
                       shippedLot.setInvoiceNumber( shipStatus.getInvoiceNo() );
                       shippedLot.setFabLotNumber( fabLotNumber );
                       shippedLot.setMawb( shipStatus.getMawb() );
                       shippedLot.setHawb( shipStatus.getHawb() );
                       shippedLot.setEtd( StringUtils.formatDate( shipStatus.getEtd(), "yyyyMMdd", "MM/dd/yyyy") );
                       shippedLot.setEta( StringUtils.formatDate( shipStatus.getEta(), "yyyyMMdd", "MM/dd/yyyy") );
                       shippedLot.setFlightNumber( shipStatus.getFlightNo() );
                       shippedLot.setProcessType( LOCATION_TYPE_ASSEMBLY );

                   }
               }
           }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            log("Exception while geting ASEM Shipping inforamtion" + e);
            throw  e;
        }
    }

    private void log(String message)
    {
        if (message == null) return;

        if (m_parserLogger != null)
        {
           synchronized (m_parserLogger)
           {
               m_parserLogger.logDebug(message + LINE_SEPARATOR);
           }
        }
        else
        {
           System.out.println(message);
        }
    }
}